﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Bitirme.DAL.Entities;
using System.Data.SqlClient;
using Bitirme.DAL.Utils;
using System.Data;


namespace Bitirme.DAL.Data_Access
{
    public class ProjectCollection
    {
        public static int InsertProject(Project project)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parName = null;
            SqlParameter parSubject = null;
            SqlParameter parDetails = null;
            SqlParameter parCost = null;
            SqlParameter parStartDate = null;
            SqlParameter parEndDate = null;
            SqlParameter parDuration = null;
            SqlParameter parProjectManagerID = null;
            SqlParameter parProjectState = null;
            SqlParameter parReturnValue = null;

            int insertedProjectID = -1;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("InsertProject", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??

                parName = new SqlParameter();
                parName.ParameterName = "@name";
                parName.Value = project.Name;
                parName.SqlDbType = SqlDbType.VarChar;

                parSubject = new SqlParameter();
                parSubject.ParameterName = "@subject";
                parSubject.Value = project.Subject;
                parSubject.SqlDbType = SqlDbType.VarChar;

                parDetails = new SqlParameter();
                parDetails.ParameterName = "@details";
                parDetails.Value = project.Details;
                parDetails.SqlDbType = SqlDbType.VarChar;

                parCost = new SqlParameter();
                parCost.ParameterName = "@cost";
                parCost.Value = project.Cost;
                parCost.SqlDbType = SqlDbType.Decimal;

                parStartDate = new SqlParameter();
                parStartDate.ParameterName = "@startdate";
                parStartDate.Value = project.StartDate;
                parStartDate.SqlDbType = SqlDbType.DateTime;

                parEndDate = new SqlParameter();
                parEndDate.ParameterName = "@enddate";
                parEndDate.Value = project.EndDate;
                parEndDate.SqlDbType = SqlDbType.DateTime;

                parDuration = new SqlParameter();
                parDuration.ParameterName = "@duration";
                parDuration.Value = project.Duration;
                parDuration.SqlDbType = SqlDbType.Int;

                parProjectManagerID = new SqlParameter();
                parProjectManagerID.ParameterName = "@projectmanagerID";
                parProjectManagerID.Value = project.Manager.ID;
                parProjectManagerID.SqlDbType = SqlDbType.Int;

                parProjectState = new SqlParameter();
                parProjectState.ParameterName = "@projectstate";
                parProjectState.Value = project.ProjectState;
                parProjectState.SqlDbType = SqlDbType.Int;

                parReturnValue = new SqlParameter();
                parReturnValue.Direction = ParameterDirection.ReturnValue;
                parReturnValue.ParameterName = "@ID";
                
                cmd.Parameters.Add(parName);
                cmd.Parameters.Add(parSubject);
                cmd.Parameters.Add(parDetails);
                cmd.Parameters.Add(parCost);
                cmd.Parameters.Add(parStartDate);
                cmd.Parameters.Add(parEndDate);
                cmd.Parameters.Add(parDuration);
                cmd.Parameters.Add(parProjectManagerID);
                cmd.Parameters.Add(parProjectState);
                cmd.Parameters.Add(parReturnValue);

                //insertedProjectID = project.ID;
                myConnection.Open();
                cmd.ExecuteScalar();
                Int32.TryParse(parReturnValue.Value.ToString(), out insertedProjectID);
            }
            catch (SqlException e)
            {
                insertedProjectID = -1;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }


            return insertedProjectID;
        }

        public static int UpdateProjectByID(Project project)
        {

            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parID = null;
            SqlParameter parName = null;
            SqlParameter parSubject = null;
            SqlParameter parDetails = null;
            SqlParameter parCost = null;
            SqlParameter parStartDate = null;
            SqlParameter parEndDate = null;
            SqlParameter parDuration = null;
            SqlParameter parProjectManagerID = null;
            SqlParameter parProjectState = null;

            int updatedprojectID=-1;
            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("UpdateProjectByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection; // ??


                parID = new SqlParameter();
                parID.ParameterName = "@ID";
                parID.Value = project.ID;
                parID.SqlDbType = SqlDbType.Int;

                parName = new SqlParameter();
                parName.ParameterName = "@name";
                parName.Value = project.Name;
                parName.SqlDbType = SqlDbType.VarChar;

                parSubject = new SqlParameter();
                parSubject.ParameterName = "@subject";
                parSubject.Value = project.Subject;
                parSubject.SqlDbType = SqlDbType.VarChar;

                parDetails = new SqlParameter();
                parDetails.ParameterName = "@details";
                parDetails.Value = project.Details;
                parDetails.SqlDbType = SqlDbType.VarChar;

                parCost = new SqlParameter();
                parCost.ParameterName = "@cost";
                parCost.Value = project.Cost;
                parCost.SqlDbType = SqlDbType.Decimal;

                parStartDate = new SqlParameter();
                parStartDate.ParameterName = "@startdate";
                parStartDate.Value = project.StartDate;
                parStartDate.SqlDbType = SqlDbType.Date;

                parEndDate = new SqlParameter();
                parEndDate.ParameterName = "@enddate";
                parEndDate.Value = project.EndDate;
                parEndDate.SqlDbType = SqlDbType.Date;

                parDuration = new SqlParameter();
                parDuration.ParameterName = "@duration";
                parDuration.Value = project.Duration;
                parDuration.SqlDbType = SqlDbType.Int;

                parProjectManagerID = new SqlParameter();
                parProjectManagerID.ParameterName = "@projectmanagerID";
                parProjectManagerID.Value = project.Manager.ID;
                parProjectManagerID.SqlDbType = SqlDbType.Int;

                parProjectState = new SqlParameter();
                parProjectState.ParameterName = "@projectstate";
                parProjectState.Value = project.ProjectState;
                parProjectState.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parID);
                cmd.Parameters.Add(parName);
                cmd.Parameters.Add(parSubject);
                cmd.Parameters.Add(parDetails);
                cmd.Parameters.Add(parCost);
                cmd.Parameters.Add(parStartDate);
                cmd.Parameters.Add(parEndDate);
                cmd.Parameters.Add(parDuration);
                cmd.Parameters.Add(parProjectManagerID);
                cmd.Parameters.Add(parProjectState);


                myConnection.Open();
                cmd.ExecuteScalar();
                updatedprojectID = project.ID;
            }
            catch (SqlException e)
            {
                updatedprojectID = -1;
                Console.WriteLine(e);
            }
            finally
            {

                if (myConnection != null)
                {

                    myConnection.Close();
                    myConnection.Dispose();
                }

            }
            return updatedprojectID;
        }
 
        public static Boolean DeleteProjectByID(int projectID)
        {
            SqlConnection myConnection = null;
            SqlCommand cmd = null;
            SqlParameter parProjectID = null;
            bool isDeleteSuccessful = false;

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("DeleteProjectByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parProjectID = new SqlParameter();
                parProjectID.ParameterName = "@ID";
                parProjectID.Value = projectID;
                parProjectID.SqlDbType = SqlDbType.Int;


                cmd.Parameters.Add(parProjectID);


                myConnection.Open();

                cmd.ExecuteScalar();
                isDeleteSuccessful = true;
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
                isDeleteSuccessful = false;
            }

            finally
            {

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return isDeleteSuccessful;
        }

        public static List<Project> GetAllProjects()
        {
            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            List<Project> projects = new List<Project>();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("GetAllProjects", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                while (myReader.Read())
                {
                    Project tempProject = new Project();

                    int ID;
                    Int32.TryParse(SQLUtils.NVL(myReader["ID"], 0).ToString(), out ID);
                    tempProject.ID = ID;

                    tempProject.Name = SQLUtils.NVL(myReader["Name"], String.Empty).ToString();

                    tempProject.Subject = SQLUtils.NVL(myReader["Subject"], String.Empty).ToString();

                    tempProject.Details = SQLUtils.NVL(myReader["DETAIL"], String.Empty).ToString();

                    int cost;
                    Int32.TryParse(SQLUtils.NVL(myReader["Cost"], 0).ToString(), out cost);
                    tempProject.Cost = cost;

                    int duration;
                    Int32.TryParse(SQLUtils.NVL(myReader["DURATION"], 0).ToString(), out duration);
                    tempProject.Duration = duration;

                    tempProject.EndDate = Convert.ToDateTime(myReader["END_DATE"].ToString());
                    tempProject.StartDate = Convert.ToDateTime(myReader["START_DATE"].ToString());

                    projects.Add(tempProject);
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }

            return projects;
        }

        public static Project SelectProjectByID(int id) {

            SqlConnection myConnection = null;
            SqlDataReader myReader = null;
            SqlCommand cmd = null;
            SqlParameter parID = null;
            Project project = new Project();

            try
            {
                myConnection = SQLUtils.GetConnection();
                cmd = new SqlCommand("SelectProjectByID", myConnection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                parID = new SqlParameter();
                parID.ParameterName = "@ID";
                parID.Value = id;
                parID.SqlDbType = SqlDbType.Int;

                cmd.Parameters.Add(parID);

                myConnection.Open();
                myReader = cmd.ExecuteReader();
                if (myReader.Read())
                {
                    project.ID = id;
                    project.Name = SQLUtils.NVL(myReader["NAME"], String.Empty).ToString();
                    project.Details = SQLUtils.NVL(myReader["DETAIL"], String.Empty).ToString();
                    project.Subject = SQLUtils.NVL(myReader["SUBJECT"], String.Empty).ToString();
                    int cost;
                    Int32.TryParse(SQLUtils.NVL(myReader["COST"], 0).ToString(), out cost);
                    project.Cost = cost;
                    int duration;
                    Int32.TryParse(SQLUtils.NVL(myReader["DURATION"], 0).ToString(), out duration);
                    project.Duration = duration;

                    project.EndDate = Convert.ToDateTime(myReader["END_DATE"].ToString());
                    project.StartDate = Convert.ToDateTime(myReader["START_DATE"].ToString());
       
                }
            }

            catch (SqlException e)
            {
                Console.WriteLine(e);
            }

            finally
            {
                if (myReader != null)
                {
                    myReader.Close();
                    myReader.Dispose();
                }

                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection.Dispose();
                }
            }
            return project;
        }
    }
}